สูตร Google Sheets(Excel) มันยาก หรือเราทำให้ลำบากเอง
Table of Contents
ก่อนอื่นต้องบอกเลยว่าบทความนี้ไม่ได้จะมาบอกว่าสูตรใน Google Sheets หรือ Excel มันจะง่ายตลอดละครับ แต่ผมจะมาพาคุณสำรวจวิธีออกแบบตารางเก็บข้อมูลของคุณว่ากำลังสร้างความยากลำบากในอนาคตอยู่หรือเปล่า เพราะการเก็บข้อมูลเราไม่ได้เก็บไปเฉยๆแต่เราต้องเอามันไปใช้งานด้วยจริงไหม?
วิธีสำรวจวิธีออกแบบตารางในวันนี้ก็ไม่ได้ซับซ้อนอะไรมากมายเลย มีแค่ไม่กี่ข้อเท่านั้นเองเดี๋ยวเรามาลองดูกันครับ
คุณเคยทำแบบนี้หรือไม่? #
1. วางชุดข้อมูลต่อกัน column แทนที่จะวางต่อกันใน row #
การทำแบบนี้ดูสบายตาแต่ละนึกถึงการขยายของข้อมูล ถ้ามีสินค้าเพิ่มหรือจาก 3 เดือนกลายเป็น 1 ปี ตารางจะกว้างออกไปมากแค่ไหน
2. แยกตารางโดยไม่จำเป็น #
ต่อจากข้อแรก พอคุณรู้สึกว่ามีข้อมูลเป็นคนละชุดกัน คุณก็จะแยกตารางออกมาเพื่อให้ดูเรียบร้อยสบายตา แต่ถ้าถามว่า “เดือนไหนมียอดขายรวมสูงที่สุด?” คุณจะต้องเขียนสูตรยังไง แล้วถ้ามีสาขาเพิ่มขึ้นอีก 10 สาขาล่ะ ต้องแก้สูตรตามกันยาวเลย
3. รวมข้อมูลที่จำเป็นต้องคิดแยกกันใน cell เดียวกัน #
วิธีง่ายในการอ่านและกรอกข้อมูล แต่ถ้าเกิดอยากรู้ว่า 08:00 - 12:00 นี่มันกี่ชั่วโมง คุณต้องใช้สูตรเพื่อแยกมันออกจากการเพื่อเอามาหาผลต่าง แทนที่จะสั่งเอา 2 cell ลบกันได้เลย
4. ใส่ข้อมูลคนละประเภทในชุดข้อมูลเดียวกัน #
นึกภาพตามจากข้อก่อนหน้านะครับ สมมติว่าผมต้องเขียนสูตรแยกเวลา 2 ก้อนออกจากกัน ตอนที่มันมาเจอคำว่า “ขาด” จะเกิดอะไรขึ้น
แค่คุณทำตาม 4 ข้อนี้จากการใช้สูตรง่ายๆสั้นๆ คุณจะได้เพิ่มความมันส์ในการทำงานอีกมหาศาลเลยครับ ใครยังไม่เห็นภาพลองมาดูตัวอย่างกันครับ
ตัวอย่างที่ 1 ร้านค้า สาขา และยอดขาย #
นี่คือตารางที่พนักงานคนนึงสร้างไว้ เนื่องจากร้านมีสินค้าไม่เยอะมาก และมีสาขาแค่ 4 สาขา พนักงานคนนี้เลยทำตารางเล็กๆมารวมกันไว้ใน sheet เดียว โดยหวังว่าการที่ดูง่ายแบบนี้คงทำให่้จัดการอะไรๆง่ายหน่อย หลังจากนั้นเขาก็เสนอให้ผู้บริหารแล้วได้รับ feedback ว่าให้เพิ่มเติมข้อมูลดังนี้
สิ่งที่ผู้บริหารอยากเห็น #
- สินค้าขายดีเรียงจากมากไปหาน้อย
- สาขาที่ขายดีเรียงจากมากไปหาน้อย
- ยอดขายรวมทุกสาขาในแต่ละเดือน
สูตรที่ใช้เพื่อให้ได้ข้อมูลที่ผู้บริหารต้องการ #
- สินค้าขายดีเรียงจากมากไปหาน้อย ก็ใช้
SUM()ยอดของแต่ละสาขา แล้วก็เอามาบวกกัน หลังจากนั้นก็ยัดลง{}แล้วสั่งSORT()ก็จบแล้วยากตรงไหน (หรอ?)
=SORT({
B5,SUM(C5:E5)+SUM(H5:J5)+SUM(C14:E14)+SUM(H14:J14);
B6,SUM(C6:E6)+SUM(H6:J6)+SUM(C15:E15)+SUM(H15:J15);
B7,SUM(C7:E7)+SUM(H7:J7)+SUM(C16:E16)+SUM(H16:J16);
B8,SUM(C8:E8)+SUM(H8:J8)+SUM(C17:E17)+SUM(H17:J17);
B9,SUM(C9:E9)+SUM(H9:J9)+SUM(C18:E18)+SUM(H18:J18)
},2,false)
- สาขาที่ขายดีเรียงจากมากไปหาน้อย อันนี้ก็ง่ายขึ้นมาอีก
SUM()ตาราง 4 ตารางแล้วยัดเข้า{}จัดSORT()ก็จบโธ่เอ้ย มันลำบากตรงไหน?
=SORT({
B2,SUM(C5:E9);
B11,SUM(C14:E18);
G2,SUM(H5:J9);
G11,SUM(H14:J18)
},2,false)
- ยอดขายรวมทุกสาขาในแต่ละเดือน คล้ายๆข้อแรกแหละ แค่เปลี่ยนมาจิ้มเดือนแทนที่จะเป็นสินค้า มีแค่ 3 เดือนไม่ทันตาลายหรอก
=SORT({
C4,SUM(C5:C9)+SUM(C14:C18)+SUM(H5:H9)+SUM(H14:H18);
D4,SUM(D5:D9)+SUM(D14:D18)+SUM(I5:I9)+SUM(I14:I18);
E4,SUM(E5:E9)+SUM(E14:E18)+SUM(J5:J9)+SUM(J14:J18)
},2,false)
บางคนอาจจะมองว่าก็ไม่แย่นี่… ครับไม่แย่เล้ยยย ได้คำตอบอย่างที่ผู้บริหารต้องการสบายมาก แต่ถ้าเราลองเพิ่มจาก 3 เดือน เป็นรายงานประจำปีดูสิครับสูตรจะยาวขึ้นอีกนะ เท่านั้นไม่พอถ้าเกิดมีสาขาใหม่เพิ่มมาอีก 10 สาขาชีวิตคุณจะเป็นยังไงกับการใช้ตารางแบบนี้
จะดีกว่าไหมถ้าเปลี่ยนมาเก็บข้อมูลแบบนี้ #
- เปลี่ยนจากสาขาละตารางมาอยู่ในตารางเดียว
- แทนที่จะเอาชื่อเดือนไปอยู่บนหัว column ลองเปลี่ยนมาอยู่ในแถวดูดีไหม เผื่อว่าเวลาใช้สูตรที่มีเงื่อนไขเราจะได้กำหนดชื่อเดือนได้เลย อ้อ! สาขาก็เหมือนกันนะ
- ได้ตารางที่ขยายออกในแนวตั้งทางเดียวไม่ว่าจะมีสินค้าใหม่ สาขาใหม่ หรือเดือนอื่นๆ มันก็แค่เพิ่มแถวเท่านั้นเอง
- ใช่ครับดูไม่สบายตาเลย ไม่เห็นภาพรวม กวาดตาดูไม่รู้เรื่องเลยสักนิด แต่อย่าลืมนะครับว่าเราเก็บข้อมูลเพื่อเอามาใช้กับสูตร ไม่ได้เอามาดูจริงไหม
มาดูสูตรที่ใช้กับตารางนี้กัน #
เนื่องจากข้อมูลของเรามันถูกรวมไว้ที่เดียว แล้วก็ยังมีรูปแบบเดียวกันทั้งหมดการสร้างรายงานให้ผู้บริหารชุดนี้ เราใช่แค่สูตร =QUERY() ตัวเดียวเลยครับ
- สินค้าขายดีเรียงจากมากไปหาน้อย ผมสั่งเลือก column
Bซึ่งก็คือ “สินค้า” และSUM(C)คือ “ผลรวมของยอดขาย” มาแสดงโดยจัดกลุ่มมันด้วยชื่อสินค้าgroup by Bแล้วก็เรียงจากมากไปหาน้อยorder by SUM(C)จากช่วงข้อมูลA1:Dเห็นไหมว่ามันสั้นลงเยอะ
=QUERY(A1:D,"select B, SUM(C) group by B order by SUM(C) desc")
- สาขาที่ขายดีเรียงจากมากไปหาน้อย คล้ายกับสินค้าขายดีเลยครับ แค่เปลี่ยนมาแสดง
Aและgroup by Aเพราะเราพิจารณาเป็นสาขา ก็จะได้คำตอบแล้ว
=QUERY(A1:D,"select A, SUM(C) group by A order by SUM(C) desc")
- ยอดขายรวมทุกสาขาในแต่ละเดือน เช่นกันกับ 2 ข้อที่ผ่านมาครับ เปลี่ยนมาพิจารณา
Dซึ่งคือเดือนนั่นเอง
=QUERY(A1:D,"select D, SUM(C) group by D order by SUM(C) desc")
เห็นไหมครับว่าถึงจะขัดสายตาในการเก็บ แต่เราใช้คำสั่งที่เข้าใจง่ายแถมสั้นลงอีกต่างหาก ที่สำคัญคือถ้าคุณเก็บข้อมูลแบบตารางนี้ ต่อให้เพิ่มสินค้าหรือสาขาคุณก็ไม่ต้องแก้สูตรพวกนี้เลย
ตัวอย่างที่ 2 พนักงาน วันที่และเวลาเข้างาน #
นายจ้างคนหนึ่งกรอกเวลาเข้า-ออกงานในแต่ละกะของลูกจ้างจำนวน 5 คน เป็นเวลา 6 วัน โดยหวังว่าจะนำข้อมูลที่ได้มาหาตัวเลขดังนี้
ข้อมูลที่นายจ้างอยากได้ #
- เวลาของแต่ละคนในแต่ละวัน
- พนักงานคนไหนขาดงานบ้างในสัปดาห์นี้
- พนักงานแต่ละคนทำงานกี่วันในสัปดาห์นี้
- แต่ละวันมีคนมาทำงานกี่คน
สูตรที่ใช้เพื่อให้ได้ข้อมูลที่นายจ้างต้องการ #
- เวลาของแต่ละคนในแต่ละวัน เนื่องจากการเก็บข้อมูลเวลาเข้าออกในแต่ละกะไว้ cell เดียวกันต้องแยกกันออกมาด้วยคำสั่ง
=SPLIT(B2, " - ")โดยใช้ขีดคั่นเวลาเป็นตัวแบ่ง แล้วค่อยเอาเวลาที่ แยกได้มาลบกันแล้วค่อยเอาผลต่างกะ “เช้า” และ “บ่าย” มารวมกันเพื่อให้ได้เวลาทำงานทั้งวัน แล้วก็ทำอย่างนี้วนไปเรื่อยๆทีละวัน แปลว่าเราต้องเพิ่มพื้นที่ column ออกไปเรื่อยๆเห็นท่าไม่ดี ผมเลยแยกเป็นตารางเล็กๆออกมาดีกว่า
=ARRAYFORMULA({SPLIT(B4:B8," - "),SPLIT(C4:C8," - ")})
แต่การแยกออกมาก็ดูเหมือนจะไม่ได้ช่วยอะไรมากมาย เอาเป็นว่าผมยอมแพ้ละกันขอหยุดไว้ที่ เวลารวมของแต่ละคนในแต่ละวันพอก็แล้ว ถ้าจะต้องไปหาคนขาดงาน หรือนับจำนวนคนมาทำงานอีกผมว่ายับแน่ๆ ยังไงมันก็ต้องทำตารางใหม่อยู่ดี (เห็นภาพตอนคิดเงินเดือนทั้งเดือนแล้วสยอง)
จะดีกว่าไหมถ้าทำแบบนี้ #
- ย้ายวันที่จากที่อยู่หัว column มาอยู่ใน row แทน
- แยกเวลาเข้าออกของแต่ละกะไว้คนละ cell
- วันไหนขาดก็เว้นว่างไว้เลยไม่ต้องเติมคำว่า “ขาด” เพราะข้อมูลชุดนี้ใส่แค่เวลาอย่างเดียวเวลาคำนวณจะได้ง่าย
สูตรที่ใช้เพื่อให้ได้ข้อมูลที่นายจ้างต้องการ #
ก่อนจะหาคำตอบผมขอเพิ่ม column เพื่อรวมเวลาเป็นชั่วโมงและนาทีให้ได้ก่อน เพราะสำคัญกับการทำสรุปครับ โดยการหาผลต่างระหว่างการเข้าออกแล้วจับบวกกันระหว่างกะเฉยๆนี่แหละครับ แต่ยัดใส่ =ARRAYFORMULA() เพื่อความสะดวกเท่านั้นเองจะได้ไม่ต้องลากสูตรให้เสียเวลา
=ARRAYFORMULA((D2:D-C2:C)+(F2:F-E2:E))
เพื่อให้ได้เวลาที่ชัดเจนที่สุดผมจะคำนวณออกมาเป็นหน่วยเวลาที่เล็กที่สุดของตารางนี้คือเป็น “นาที” แต่ก่อนจะได้เวลาแบบนั้นเราต้องดึงชั่วโมงและนาทีออกมาก่อน
=ARRAYFORMULA({HOUR(G2:G),MINUTE(G2:G)})
หลังจากนั้นก็เอา ชั่วโมง คูณ 60 แล้วบวกกับ นาที ก็จะได้เวลารวมในแต่ละวันพร้อมใช้งาน จะเห็นว่าคนที่ขาดงานเวลาก็จะเป็น 0 เองอยู่แล้ว
=ARRAYFORMULA((H2:H*60)+I2:I)
สำหรับใครที่มองว่ามันยากให้เอา ARRAYFORMULA ออกได้นะครับ แล้วก็ใช้การลากสูตรจากบนลงล่างได้ตามปกติครับ สูตรจะสั้นลงแต่ต้องแลกกับการที่เราต้องลากสูตรเองเวลามีการเพิ่มแถว
หลังจากได้เวลาสุทธิมาแล้วที่เหลือสูตร =QUERY ตัวเดียวก็เอาอยู่แล้วครับ
- เวลาของแต่ละคนในแต่ละวัน อยากได้เวลารวมเราก็
SUM(J)แล้วgroup by Aเพราะเราต้องการมองเป็นรายบุคคลนั่นเองครับ
=QUERY(A1:J31, "select A, SUM(J) group by A order by SUM(J) desc label SUM(J) 'เวลารวม(นาที)'")
- พนักงานคนไหนขาดงานบ้างในสัปดาห์นี้ ใครขาดงานดูง่ายๆด้วยการ นับ
COUNTเอาแถวที่J = 0นั่นเองครับ เราดูเป็นรายบุคคลดังนั้นอย่าลืมgroup by A
=QUERY(A1:J31, "select A, COUNT(J) where J = 0 group by A label COUNT(J) 'วันขาดงาน'")
- พนักงานแต่ละคนทำงานกี่วันในสัปดาห์นี้ เมื่อกี้ขาดงาน
J = 0ถ้ามาทำงานก็ต้องJ > 0ใช่ไหมครับ
=QUERY(A1:J31, "select A, COUNT(J) where J > 0 group by A label COUNT(J) 'วันมาทำงาน'")
- แต่ละวันมีคนมาทำงานกี่คน นับแถวที่
J > 0โดยเปลี่ยนมุมมองจากบุคคลมาเป็นรายวันก็group by Bแทนครับ
=QUERY(A1:J31, "select B, COUNT(A) where J > 0 group by B label COUNT(A) 'จำนวนคนเข้างาน'")
สรุปง่ายๆว่า #
ตารางเก็บข้อมูลที่ดีควรเก็บลงไปในแนวดิ่ง โดยมีคุณสมบัติของชุดข้อมูลถูกวางในแนวขวาง ดังรูปครับ
นี่คือพื้นฐานสุดๆที่คนใช้ Spreadsheet ทุกยี่ห้อควรจะรู้ไว้ก่อนจะเริ่มเก็บข้อมูลในงานต่างๆเลยนะครับ ถ้าเรื่องนี้ยังทำได้ไม่ดี การจะต่อยอดไปถึงการรวมข้อมูลจากหลายๆ sheet เพื่อหาผลลัพธ์ร่วมกันจะยิ่งยากไปใหญ่ ก็อย่างที่เห็นครับแต่ตารางเดียวบางทียังต้องถอดใจเลยครับ